import sys
import os
import numpy as np

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import dws_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def find_error(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    window_size = event.get('window_size')
    if not window_size:
        window_size = '7'
    rownum = str(np.array([i for i in range(int(window_size))]).sum())
    coldtempreture = event.get('coldtempreture')
    if not coldtempreture:
        coldtempreture = '14'
    freezetempreture = event.get('freezetempreture')
    if not freezetempreture:
        freezetempreture = '3'
    coldvaporationtempreture = event.get('coldvaporationtempreture')
    if not coldvaporationtempreture:
        coldvaporationtempreture = '0'
    freezeevaporationtempreture = event.get('freezeevaporationtempreture')
    if not freezeevaporationtempreture:
        freezeevaporationtempreture = '-20'
    appear_num = event.get('appear_num')
    if not appear_num:
        appear_num = '3'
    table_name = sys._getframe().f_code.co_name
    delete_data(dws_bucket,table_name, date, False)
    sql = f'''insert into dws.find_error
                (
                select
                          t5.sn,
                          t5.material,
                          t6.model,
                          t5.window_size,
                          t5.occurrences,
                          t5.cold,
                          t5.freeze,
                          t5.chang,
                          t5.evap,
                          t5.f_date
                    from
                    (
                        select sn,material,4 as window_size, count(sn) as occurrences, cast( date('{date}') - interval '1' day as varchar) as f_date,
                               avg(cold_abs) as cold, avg(freeze_abs) as freeze, avg(chang_abs) as chang, avg(freeze_evap_abs) as evap
                        from
                        (
                            select sn, material, row_number() over (partition by sn order by p_date) as rownum,
                                   cold_abs, freeze_abs, chang_abs, freeze_evap_abs, p_date
                            from
                            (
                                select sn, material, p_date,
                                    avg(abs(coldRoomTargetTempreture   -   coldRoomSensorTempreture ))  as cold_abs,
                                    avg(abs(freezeRoomTargetTempreture -   freezeRoomSensorTempreture)) as freeze_abs,
                                    avg(abs(changingRoomTargetTempreture - changingSensorTempreture  )) as chang_abs,
                                    avg(abs(freezeRoomTargetTempreture -   freezeRoomEvaporationSensorTempreture)) as freeze_evap_abs
                                from(
                                       select
                                            sn,
                                            material,
                                            coldRoomSensorTempreture,
                                            freezeRoomSensorTempreture,
                                            changingSensorTempreture,
                                            evnSensorTempreture,
                                            coldRoomEvaporationSensorTempreture,
                                            changingRoomEvaporationSensorTempreture,
                                            freezeRoomEvaporationSensorTempreture,
                                            coldRoomTargetTempreture,
                                            freezeRoomTargetTempreture,
                                            changingRoomTargetTempreture,
                                            humiditySensorValue,
                                            starttime,
                                            p_date
                                       from
                                            ods.iot_fridge
                                       where
                                            date(p_date) >= (cast ('{date}' as date) - interval '4' day)
                                            and
                                            date(p_date) <   cast ('{date}' as date)
                                            and
                                                abs(coldRoomSensorTempreture)    < 50
                                            and
                                                abs(freezeRoomSensorTempreture)  < 50
                
                                            and
                                                 (
                                                    coldRoomTargetTempreture  <= 8
                                                    and
                                                    coldRoomTargetTempreture  >=  2
                                                 )
                                            and
                                                 (
                                                    freezeRoomTargetTempreture  <= -16
                                                    and
                                                    freezeRoomTargetTempreture  >= -24
                                                 )
                                            and
                
                                                abs(freezeRoomEvaporationSensorTempreture) < 50
                                            and
                                                abs(evnSensorTempreture)  < 50
                                            and
                                                sn not like '%XC%'
                                            and
                                                coldDoor is null
                                            and
                                                coldRoomAirDoorHeat is null
                                            and
                                                condensationHeat is null
                                            and
                                                freezeDoor is null
                                            and
                                                freezeHeat is null
                                            and
                                                changingDoor is null
                                            and
                                                changingHeat is null
                                            and
                                                freezeDoorB is null
                                            and
                                                freezeDoorA is null
                                )t1
                
                                --需要考虑fault的情况
                                 group by material, sn,  p_date
                                 having
                                      count(coldRoomSensorTempreture)  >  100
                                 and
                                      count(freezeRoomSensorTempreture) >  100
                                 and
                                      count(coldRoomTargetTempreture)  >   100
                                 and
                                      count(freezeRoomTargetTempreture) >  100
                                 and
                                      count(freezeRoomEvaporationSensorTempreture)> 100
                             )t2
                             where
                              (
                                (
                                    cold_abs   > 8
                                    or
                                    freeze_abs >  10
                                    or
                                    freeze_evap_abs > 12
                                )
                              )
                         )t4
                        group by sn,material
                        having
                            count(sn)  >=  3
                        and
                            sum(rownum) = 10
                 )t5
                left join
                    dim.material t6
                on t5.material = t6.material
            )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        find_error(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

